package com.sec.etech.chain.customer.dao;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;

/**
 * @version : 1.0
 * @auther : 老邓
 * @date : 2020/12/17 10:42
 * @description:
 */
@Component
public class CusCustomerDao {

    @Autowired
    JdbcTemplate jdbcTemplate;

    //根据customername获取customerid
    public List<String> getCustomeridByName(String customername){
        String sql = "SELECT customerid FROM proj_customer WHERE customername=?";
        List<String> listData = jdbcTemplate.queryForList(sql, new Object[]{customername}, String.class);
        return listData;
    }

    //获取最新的客户代号
    public String getNewCustomercode() {
        String sql = "SELECT customercode FROM proj_customer ORDER BY customercode DESC LIMIT 1";
        String lastCode = jdbcTemplate.queryForObject(sql, String.class);
        return "CUS" + String.format("%5d", Integer.valueOf(lastCode.substring(lastCode.length() - 5)) + 1).replace(" ", "0");
    }

    //删除客户
    public void del(String customerid) {
        String sql = "UPDATE proj_customer SET active=0 WHERE customerid=?";
        jdbcTemplate.update(sql, customerid);
    }

    //获取关联项目
    public List<Long> getProjects(String customerid) {
        String sql = "SELECT projectid FROM proj_project WHERE customerid=?";
        return jdbcTemplate.queryForList(sql, new Object[]{customerid}, Long.class);
    }

    //编辑关联项目
    public void addProjects(String customerid, String projectids) {
        String sql = "UPDATE proj_project SET customerid=0 WHERE customerid=?;";
        sql += "UPDATE proj_project SET customerid=? WHERE projectid IN (" + projectids + ")";
        List<Object> paramList = new ArrayList<>();
        paramList.add(customerid);
        paramList.add(customerid);
        jdbcTemplate.update(sql, paramList.toArray());
    }

    //获取最新的客户机组数量
    public String getOrdersCount(String customerid) {
        String sql = "SELECT COUNT(*) FROM proj_project AS a,proj_project_orders AS b WHERE a.projectid=b.projectid AND a.customerid=?";
        List<String> listData = jdbcTemplate.queryForList(sql, new Object[]{customerid}, String.class);
        if (listData.isEmpty()) return "0";
        else return listData.get(0);
    }

    //更新所有客户机组数量
    public void resetOrdersCount() {
        String sql = "UPDATE proj_customer SET ordersCount=(SELECT COUNT(*) FROM proj_project AS a,proj_project_orders AS b " +
                "WHERE a.projectid=b.projectid AND a.customerid=proj_customer.customerid)";
        jdbcTemplate.update(sql);
    }
}
